<?php

namespace app\admin\service;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use app\model\DeliveryData;
use app\model\DailyDeliveryData;
use app\model\ResponsibilityPerson;
use app\model\Link;
use app\model\Videos;
use think\facade\Db;

class ExportService
{
    private $deliveryDataModel;
    private $dailyDeliveryDataModel;
    private $responsibilityPersonModel;
    private $videosModel;
    private $linkModel;

    public function __construct(DeliveryData $deliveryDataModel, DailyDeliveryData $dailyDeliveryDataModel, ResponsibilityPerson $responsibilityPersonModel, Link $linkModel, Videos $videosModel)
    {
        $this->deliveryDataModel = $deliveryDataModel;
        $this->dailyDeliveryDataModel = $dailyDeliveryDataModel;
        $this->responsibilityPersonModel = $responsibilityPersonModel;
        $this->linkModel = $linkModel;
        $this->videosModel = $videosModel;
    }

    // 投放总览导出
    public function launchOverviewExport($params)
    {
        $where = [];
        if (!empty($params['start_date'])) {
            $where[] = ['date', '>=', date('Ymd', strtotime($params['start_date']))];
        }
        if (!empty($params['end_date'])) {
            $where[] = ['date', '<=', date('Ymd', strtotime($params['end_date']))];
        }
        if (!empty($params['platform'])) {
            $where[] = ['platform', '=', $params['platform']];
        }

        $data = $this->deliveryDataModel->getDataByWhere($where, 'date, platform, total_cost, actual_cost, activation_user_num, current_recharge, current_recovery, total_recharge, total_recovery, fans_cost, revenue, per_capita_contribution, recharge_user_num, conversion_rate');

        if (!empty($data)) {

            $spreadsheet = new Spreadsheet();
            $worksheet = $spreadsheet->getActiveSheet();
            $worksheet->setCellValueByColumnAndRow(1, 1, '日期');
            $worksheet->setCellValueByColumnAndRow(2, 1, '花费');
            $worksheet->setCellValueByColumnAndRow(3, 1, '实际花费');
            $worksheet->setCellValueByColumnAndRow(4, 1, '激活用户数');
            $worksheet->setCellValueByColumnAndRow(5, 1, '当期充值');
            $worksheet->setCellValueByColumnAndRow(6, 1, '当期回收百分比');
            $worksheet->setCellValueByColumnAndRow(7, 1, '累计充值');
            $worksheet->setCellValueByColumnAndRow(8, 1, '累计回收百分比');
            $worksheet->setCellValueByColumnAndRow(9, 1, '粉丝成本');
            $worksheet->setCellValueByColumnAndRow(10, 1, '营收');
            $worksheet->setCellValueByColumnAndRow(11, 1, '人均贡献');
            $worksheet->setCellValueByColumnAndRow(12, 1, '充值人数');
            $worksheet->setCellValueByColumnAndRow(13, 1, '转化率');

            $line = 0;
            foreach ($data as $key => $item) {
                $date = substr($item['date'], 0, 4) . '-' . substr($item['date'], 4, 2) . '-' . substr($item['date'], 6, 2);

                $line = $key + 2;
                $worksheet->setCellValueByColumnAndRow(1, $line, $date);
                $worksheet->setCellValueByColumnAndRow(2, $line, $item['total_cost']);
                $worksheet->setCellValueByColumnAndRow(3, $line, $item['actual_cost']);
                $worksheet->setCellValueByColumnAndRow(4, $line, $item['activation_user_num']);
                $worksheet->setCellValueByColumnAndRow(5, $line, $item['current_recharge']);
                $worksheet->setCellValueByColumnAndRow(6, $line, $item['current_recovery']);
                $worksheet->setCellValueByColumnAndRow(7, $line, $item['total_recharge']);
                $worksheet->setCellValueByColumnAndRow(8, $line, $item['total_recovery']);
                $worksheet->setCellValueByColumnAndRow(9, $line, $item['fans_cost']);
                $worksheet->setCellValueByColumnAndRow(10, $line, $item['revenue']);
                $worksheet->setCellValueByColumnAndRow(11, $line, $item['per_capita_contribution']);
                $worksheet->setCellValueByColumnAndRow(12, $line, $item['recharge_user_num']);
                $worksheet->setCellValueByColumnAndRow(13, $line, $item['conversion_rate']);
            }

            $filename = './投放总览.xlsx';
            if (!empty($params['start_date']) && !empty($params['end_date'])) {
                $filename = $params['start_date'].'-'.$params['end_date'].'投放总览.xlsx';
            }

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="'.$filename.'"');
            header('Cache-Control: max-age=0');
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save('php://output');
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            exit;
        }
    }

    // 投放总览查看导出
    public function launchOverviewViewExport($params)
    {
        $date = str_replace('-', '', $params['date']);
        $where[] = ['platform', '=', $params['media_id']];

        // 短剧id
        if (!empty($params['video_id'])) {
            $linkData = $this->model->getDataByWhere(['video_id' => $params['video_id']], 'id');
            $where[] = ['link_id', 'in', array_column($linkData, 'id')];
        }

        // 链接id
        if (!empty($params['link_id'])) {
            $where[] = ['link_id', '=', $params['link_id']];
        }

        // 广告账号
        if (!empty($params['advertiser_id'])) {
            $where[] = ['advertiser_id', '=', $params['advertiser_id']];
        }

        // 负责人账号
        if (!empty($params['responsibility_person_id'])) {
            $linkData = $this->model->getDataByWhere(['responsibility_person_id' => $params['responsibility_person_id']], 'id');
            $where[] = ['link_id', 'in', array_column($linkData, 'id')];
        }

        // 公司搜索
        if (!empty($params['company_id'])) {
            $responsibilityPerson = $this->responsibilityPersonModel->getDataByWhere(['company_id' => $params['company_id']], 'id');
            $responsibilityPersonIds = array_column($responsibilityPerson, 'id');
            $linkData = $this->model->getDataByWhere(['responsibility_person_id' => $responsibilityPersonIds, 'media_id' => $params['media_id']], 'id');
            $where[] = ['link_id', 'in', array_column($linkData, 'id')];
        }

        // 搜索指定日期
        if (!empty($params['start_date'])) {
            $where[] = ['date', '>=', date('Ymd', strtotime($params['start_date']))];
        }
        if (!empty($params['end_date'])) {
            $where[] = ['date', '<=', date('Ymd', strtotime($params['end_date']))];
        }

        if (empty($params['start_date']) && empty($params['end_date'])) {
            $where[] = ['date', '=', $date];
        }

        $fields = 'date, platform, link_id, advertiser_id, cost, current_recharge, current_recovery, conversion_number, conversion_cost, new_recharge_number, new_user_number, conversion_rate, fans_cost, total_recharge, revenue, per_capita_contribution, register_user_num, actual_cost, activation_user_num';
        $data = $this->dailyDeliveryDataModel->getDataByWhere($where, $fields);

        $linkIds = array_column($data, 'link_id');
        $linkData = $this->linkModel->getDataByWhere(['id' => $linkIds], 'id, video_id, responsibility_person_id');
        $videoIds = array_column($linkData, 'video_id');
        $videoData = $this->videosModel->getDataByIds($videoIds, 'id, name');
        $video = [];
        foreach ($videoData as $key => $value) {
            foreach ($linkData as $k => $item) {
                if ($item['video_id'] == $value['id']) {
                    $video[$item['id']] = $value['name'];
                }
            }
        }

        $responsibilityPersonIds = array_column($linkData, 'responsibility_person_id');
        $responsibilityPersonData = $this->responsibilityPersonModel->getDataByWhere(['id' => $responsibilityPersonIds], 'id, name');
        $responsibilityPerson = array_column($responsibilityPersonData, 'name', 'id');

        foreach ($linkData as $key => $value) {
            $linkData[$key]['responsibility_person_name'] = $responsibilityPerson[$value['responsibility_person_id']];
        }
        $linkAndResponsibilityPersonData = array_column($linkData, 'responsibility_person_name', 'id');

        if (!empty($data)) {

            $spreadsheet = new Spreadsheet();
            $worksheet = $spreadsheet->getActiveSheet();
            $worksheet->setCellValueByColumnAndRow(1, 1, '平台');
            $worksheet->setCellValueByColumnAndRow(2, 1, '日期');
            $worksheet->setCellValueByColumnAndRow(3, 1, '链接id');
            $worksheet->setCellValueByColumnAndRow(4, 1, '广告账户');
            $worksheet->setCellValueByColumnAndRow(5, 1, '花费');
            $worksheet->setCellValueByColumnAndRow(6, 1, '实际花费');
            $worksheet->setCellValueByColumnAndRow(7, 1, '激活用户');
            $worksheet->setCellValueByColumnAndRow(8, 1, '当期充值');
            $worksheet->setCellValueByColumnAndRow(9, 1, '当期回收');
            $worksheet->setCellValueByColumnAndRow(10, 1, '累计充值');
            $worksheet->setCellValueByColumnAndRow(11, 1, '累计回收');
            $worksheet->setCellValueByColumnAndRow(12, 1, '充值人数');
            $worksheet->setCellValueByColumnAndRow(13, 1, '新充人数');
            $worksheet->setCellValueByColumnAndRow(14, 1, '新充笔数');
            $worksheet->setCellValueByColumnAndRow(15, 1, '转化成本');
            $worksheet->setCellValueByColumnAndRow(16, 1, '转换率');
            $worksheet->setCellValueByColumnAndRow(17, 1, '粉丝成本');
            $worksheet->setCellValueByColumnAndRow(18, 1, '营收');
            $worksheet->setCellValueByColumnAndRow(19, 1, '人均贡献');
            $worksheet->setCellValueByColumnAndRow(20, 1, '短剧名称');
            $worksheet->setCellValueByColumnAndRow(21, 1, '负责人');

            $line = 0;
            foreach ($data as $key => $item) {
                $date = substr($item['date'], 0, 4) . '-' . substr($item['date'], 4, 2) . '-' . substr($item['date'], 6, 2);
                $videoName = empty($video[$item['link_id']]) ? '' : $video[$item['link_id']];
                $responsibilityPersonName = empty($linkAndResponsibilityPersonData[$item['link_id']]) ? '' : $linkAndResponsibilityPersonData[$item['link_id']];

                switch ($item['platform']) {
                    case 1:
                        $media = '抖音巨量';
                        break;
                    case 2:
                        $media = '快手磁力';
                        break;
                    case 3:
                        $media = '百度营销';
                        break;
                    default:
                        $media = '未知';
                        break;
                }

                $item['actual_cost'] = intval($item['actual_cost']);
                $totalRecovery = empty($item['actual_cost']) ? '0%' : round($item['total_recharge'] / $item['actual_cost'] * 100, 2).'%';

                $line = $key + 2;
                $worksheet->setCellValueByColumnAndRow(1, $line, $media);
                $worksheet->setCellValueByColumnAndRow(2, $line, $date);
                $worksheet->setCellValueByColumnAndRow(3, $line, $item['link_id']);
                $worksheet->setCellValueByColumnAndRow(4, $line, $item['advertiser_id']);
                $worksheet->setCellValueByColumnAndRow(5, $line, $item['cost']);
                $worksheet->setCellValueByColumnAndRow(6, $line, $item['actual_cost']);
                $worksheet->setCellValueByColumnAndRow(7, $line, $item['activation_user_num']);
                $worksheet->setCellValueByColumnAndRow(8, $line, $item['current_recharge']);
                $worksheet->setCellValueByColumnAndRow(9, $line, $item['current_recovery']);
                $worksheet->setCellValueByColumnAndRow(10, $line, $item['total_recharge']);
                $worksheet->setCellValueByColumnAndRow(11, $line, $totalRecovery);
                $worksheet->setCellValueByColumnAndRow(12, $line, $item['conversion_number']);
                $worksheet->setCellValueByColumnAndRow(13, $line, $item['new_user_number']);
                $worksheet->setCellValueByColumnAndRow(14, $line, $item['new_recharge_number']);
                $worksheet->setCellValueByColumnAndRow(15, $line, $item['conversion_cost']);
                $worksheet->setCellValueByColumnAndRow(16, $line, $item['conversion_rate']);
                $worksheet->setCellValueByColumnAndRow(17, $line, $item['fans_cost']);
                $worksheet->setCellValueByColumnAndRow(18, $line, $item['revenue']);
                $worksheet->setCellValueByColumnAndRow(19, $line, $item['per_capita_contribution']);
                $worksheet->setCellValueByColumnAndRow(20, $line, $videoName);
                $worksheet->setCellValueByColumnAndRow(21, $line, $responsibilityPersonName);
            }

            $filename = './投放总览查看详情.xlsx';
            if (!empty($params['start_date']) && !empty($params['end_date'])) {
                $filename = $params['start_date'].'-'.$params['end_date'].'投放总览查看详情.xlsx';
            }

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="'.$filename.'"');
            header('Cache-Control: max-age=0');
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save('php://output');
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            exit;
        }
    }

    // 投手统计按月份查看导出
    public function byMonthViewExport($params)
    {
        $responsibilityPersonId = $params['id'];
        $where = ['responsibility_person_id' => $responsibilityPersonId];

        if (!empty($params['media_id'])) {
            $where['media_id'] = $params['media_id'];
        }

        $responsibilityPerson = $this->responsibilityPersonModel->getInfoById($responsibilityPersonId, 'name');
        $linkData = $this->linkModel->getDataByWhere($where, 'id, responsibility_person_id');

        $linkIds = array_column($linkData, 'id');

        $data = Db::name('daily_delivery_data')->field('month')->where(['link_id' => $linkIds])->group('month')->order('month desc')->select()->toArray();

        $month = array_column($data, 'month');
        $dailyDeliveryData = $this->dailyDeliveryDataModel->getDataByWhere(['link_id' => $linkIds, 'month' => $month], 'month, link_id, cost, actual_cost, register_user_num, activation_user_num, current_recharge, total_recharge, recharge_number, conversion_number, new_recharge_number, new_user_number, conversion_cost, conversion_rate, current_recovery');

        $dailyDeliveryDataResult = [];
        foreach ($dailyDeliveryData as $key => $value) {
            $dailyDeliveryDataResult[$value['month']][] = $value;
        }

        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setCellValueByColumnAndRow(1, 1, '月份');
        $worksheet->setCellValueByColumnAndRow(2, 1, '负责人');
        $worksheet->setCellValueByColumnAndRow(3, 1, '花费');
        $worksheet->setCellValueByColumnAndRow(4, 1, '实际花费');
        $worksheet->setCellValueByColumnAndRow(5, 1, '激活用户');
        $worksheet->setCellValueByColumnAndRow(6, 1, '当期充值');
        $worksheet->setCellValueByColumnAndRow(7, 1, '当期回收');
        $worksheet->setCellValueByColumnAndRow(8, 1, '累计充值');
        $worksheet->setCellValueByColumnAndRow(9, 1, '累计回收');
        $worksheet->setCellValueByColumnAndRow(10, 1, '订单数');
        $worksheet->setCellValueByColumnAndRow(11, 1, '充值人数');
        $worksheet->setCellValueByColumnAndRow(12, 1, '营收');
        $worksheet->setCellValueByColumnAndRow(13, 1, '充值用户贡献');

        $line = 0;
        foreach ($data as $key => $value) {
            $month = substr($value['month'], 0, 4).'-'.substr($value['month'], 4, 2);
            $cost = $actualCost = $registerUserNum = $activationUserNum = $currentRecharge = $totalRecharge = $rechargeNumber = $conversionNumber = $perCapitaContribution = $revenue = 0;
            $currentRecovery = $totalRecovery = '0%';

            if (!empty($dailyDeliveryDataResult[$value['month']])) {

                foreach ($dailyDeliveryDataResult[$value['month']] as $item) {
                    $cost = $cost + $item['cost'];
                    $actualCost = $actualCost + $item['actual_cost'];
                    // $registerUserNum = $registerUserNum + $item['register_user_num'];
                    $activationUserNum = $activationUserNum + $item['activation_user_num'];
                    $currentRecharge = $currentRecharge + $item['current_recharge'];
                    $totalRecharge = $totalRecharge + $item['total_recharge'];
                    $rechargeNumber = $rechargeNumber + $item['recharge_number']; // 订单数
                    $conversionNumber = $conversionNumber + $item['conversion_number'];
                }

                $currentRecovery = intval($cost) == 0 ? '0%' : round($currentRecharge / $actualCost * 100, 2).'%';
                $totalRecovery = intval($cost) == 0 ? '0%' : round($totalRecharge / $actualCost * 100, 2).'%';

                $perCapitaContribution = $conversionNumber == 0 ? 0 : round($totalRecharge / $conversionNumber, 2); // 充值用户贡献
            }

            $line = $key + 2;
            $worksheet->setCellValueByColumnAndRow(1, $line, $month);
            $worksheet->setCellValueByColumnAndRow(2, $line, $responsibilityPerson['name']);
            $worksheet->setCellValueByColumnAndRow(3, $line, $cost);
            $worksheet->setCellValueByColumnAndRow(4, $line, $actualCost);
            $worksheet->setCellValueByColumnAndRow(5, $line, $activationUserNum);
            $worksheet->setCellValueByColumnAndRow(6, $line, $currentRecharge);
            $worksheet->setCellValueByColumnAndRow(7, $line, $currentRecovery);
            $worksheet->setCellValueByColumnAndRow(8, $line, $totalRecharge);
            $worksheet->setCellValueByColumnAndRow(9, $line, $totalRecovery);
            $worksheet->setCellValueByColumnAndRow(10, $line, $rechargeNumber);
            $worksheet->setCellValueByColumnAndRow(11, $line, $conversionNumber);
            $worksheet->setCellValueByColumnAndRow(12, $line, round($totalRecharge - $actualCost, 2));
            $worksheet->setCellValueByColumnAndRow(13, $line, $perCapitaContribution);
        }

        $filename = './投手统计按月份查看.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    // 按投手搜索查看导出
    public function bySearchViewExport($params)
    {
        $where = [];

        if (!empty($params['media_id'])) {
            $where[] = ['platform', '=', $params['media_id']];
        }
        if (!empty($params['link_id'])) {
            $where[] = ['link_id', '=', $params['link_id']];
        }
        if (!empty($params['start_date'])) {
            $where[] = ['date', '>=', str_replace('-', '', $params['start_date'])];
        }
        if (!empty($params['end_date'])) {
            $where[] = ['date', '<=', str_replace('-', '', $params['end_date'])];
        }
        if (!empty($params['responsibility_person_id'])) {
            $linkData = $this->linkModel->getDataByWhere(['responsibility_person_id' => $params['responsibility_person_id']], 'id');
            $linkIds = array_column($linkData, 'id');
            $where[] = ['link_id', 'in', $linkIds];

            $responsibilityPerson = $this->responsibilityPersonModel->getInfoById($params['responsibility_person_id'], 'name');
        }

        $data = $this->dailyDeliveryDataModel->getDataByWhere($where, 'date, link_id, cost, actual_cost, register_user_num, activation_user_num, current_recharge, total_recharge, recharge_number, conversion_number, current_recovery, revenue, per_capita_contribution');

        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setCellValueByColumnAndRow(1, 1, '日期');
        $worksheet->setCellValueByColumnAndRow(2, 1, '负责人');
        $worksheet->setCellValueByColumnAndRow(3, 1, '链接ID');
        $worksheet->setCellValueByColumnAndRow(4, 1, '花费');
        $worksheet->setCellValueByColumnAndRow(5, 1, '实际花费');
        $worksheet->setCellValueByColumnAndRow(6, 1, '注册用户');
        $worksheet->setCellValueByColumnAndRow(7, 1, '激活用户');
        $worksheet->setCellValueByColumnAndRow(8, 1, '当期充值');
        $worksheet->setCellValueByColumnAndRow(9, 1, '当期回收');
        $worksheet->setCellValueByColumnAndRow(10, 1, '累计充值');
        $worksheet->setCellValueByColumnAndRow(11, 1, '累计回收');
        $worksheet->setCellValueByColumnAndRow(12, 1, '订单数');
        $worksheet->setCellValueByColumnAndRow(13, 1, '充值人数');
        $worksheet->setCellValueByColumnAndRow(14, 1, '营收');
        $worksheet->setCellValueByColumnAndRow(15, 1, '人均贡献');

        $line = 0;
        foreach ($data as $key => $value) {

            $line = $key + 2;
            $date = substr($value['date'], 0, 4).'-'.substr($value['date'], 4, 2).'-'.substr($value['date'], 6, 2);
            $totalRecovery = intval($value['cost']) == 0 ? 0 : round($value['total_recharge'] / $value['cost'] * 100, 2).'%';
            $responsibilityPersonName = empty($responsibilityPerson['name']) ? '' : $responsibilityPerson['name'];

            $worksheet->setCellValueByColumnAndRow(1, $line, $date);
            $worksheet->setCellValueByColumnAndRow(2, $line, $responsibilityPersonName);
            $worksheet->setCellValueByColumnAndRow(3, $line, $value['link_id']);
            $worksheet->setCellValueByColumnAndRow(4, $line, $value['cost']);
            $worksheet->setCellValueByColumnAndRow(5, $line, $value['actual_cost']);
            $worksheet->setCellValueByColumnAndRow(6, $line, $value['register_user_num']);
            $worksheet->setCellValueByColumnAndRow(7, $line, $value['activation_user_num']);
            $worksheet->setCellValueByColumnAndRow(8, $line, $value['current_recharge']);
            $worksheet->setCellValueByColumnAndRow(9, $line, $value['current_recovery']);
            $worksheet->setCellValueByColumnAndRow(10, $line, $value['total_recharge']);
            $worksheet->setCellValueByColumnAndRow(11, $line, $totalRecovery);
            $worksheet->setCellValueByColumnAndRow(12, $line, $value['recharge_number']);
            $worksheet->setCellValueByColumnAndRow(13, $line, $value['conversion_number']);
            $worksheet->setCellValueByColumnAndRow(14, $line, round($value['total_recharge'] - $value['actual_cost'], 2));
            $worksheet->setCellValueByColumnAndRow(15, $line, $value['per_capita_contribution']);
        }
        $filename = './按投手查看.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }
}